package Dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Dao.Daocommodity;
import entity.Commodity;
import entity.Commtype;
import entity.Imgtype;
import utils.BaseDao;

public class Daoimplcommodity extends BaseDao implements Daocommodity {

	@Override
	public List<Commodity> sel(String ming) {
		String sql = "select * from commodity where yemian = ? or yemian2 = ?";
		Object[] object = {ming,ming};
		List<Commodity> list =new ArrayList<Commodity>();
		ResultSet rs =super.executeQuery(sql, object);
		try {
			while (rs.next()) {
				Commodity cty =new Commodity();
				cty.setId(rs.getInt(1));
				cty.setName(rs.getString(2));
				cty.setMoney(rs.getString(3));
				cty.setIntroduction(rs.getString(4));
				Imgtype img = new Imgtype();
				img.setId(rs.getInt(5));		
				String sql2 = "select * from imgtype where id =?";
				Object[] object1 = {img.getId()};
				ResultSet rs2 =super.executeQuery(sql2, object1);
				while(rs2.next()){
					img.setId(rs2.getInt(1));
					img.setImg(rs2.getString(2));
					img.setType(rs2.getString(3));
				}
				try {
					super.closeAll(rs2, rs2.getStatement(), rs2.getStatement().getConnection());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				cty.setIid(img);
				cty.setType(rs.getInt(6));
				list.add(cty);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				super.closeAll(rs, rs.getStatement(), rs.getStatement().getConnection());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public List<Commodity> housel() {
		String sql="select * from `commodity`";
		List<Commodity> list =new ArrayList<Commodity>();
		ResultSet rs =super.executeQuery(sql, null);
		try {
			while (rs.next()) {
				Commodity cty =new Commodity();
				cty.setId(rs.getInt(1));
				cty.setName(rs.getString(2));
				cty.setMoney(rs.getString(3));
				cty.setIntroduction(rs.getString(4));
				
				Imgtype img = new Imgtype();
				img.setId(rs.getInt(5));		
				String sql2 = "select * from imgtype where id =?";
				Object[] object = {img.getId()};
				ResultSet rs2 =super.executeQuery(sql2, object);
				while(rs2.next()){
					img.setId(rs2.getInt(1));
					img.setImg(rs2.getString(2));
					img.setType(rs2.getString(3));
				}
				try {
					super.closeAll(rs2, rs2.getStatement(), rs2.getStatement().getConnection());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
				cty.setIid(img);
				cty.setType(rs.getInt(6));
				int type1 = cty.getType();
				
				
				
				Commtype type2 = new Commtype();
				type2.setId(type1);
				String sql3 = "select * from commtype where id =?";
				Object[] object2 = {type2.getId()};
				ResultSet rs3 =super.executeQuery(sql3, object2);
				while(rs3.next()){
					type2.setName(rs3.getString(2));
				}
				try {
					super.closeAll(rs3, rs3.getStatement(), rs3.getStatement().getConnection());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				cty.setType1(type2);
				
				cty.setXiaoliang(rs.getInt(7));
				cty.setShijian(rs.getTimestamp(10).toString());
				list.add(cty);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				super.closeAll(rs, rs.getStatement(), rs.getStatement().getConnection());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	@Override
	public boolean houdel(int id) {
		String sql = "delete from `xiangmu`.`commodity` where `id` = ?";
		Object[] object = {id};
		return super.executeUpdate(sql, object);
	}

	@Override
	public boolean addsp(Commodity comm) {
		String  sql = "insert into `xiangmu`.`commodity` (`name`, `money`, `introduction`, `iid`, `type`, `yemian`) values (?, ?, ?, ?, ?,?)"; 
		Object[] object = {comm.getName(),comm.getMoney(),comm.getIntroduction(),comm.getIiid(),comm.getType(),comm.getYemian()};
		return super.executeUpdate(sql, object);
	}

	@Override
	public Commodity sel(int id) {
		String sql = "select * from commodity where id = ?";
		Object[] object = {id};
		ResultSet rs =super.executeQuery(sql, object);
		Commodity cty = null;
		try {
			while (rs.next()) {
				cty =new Commodity();
				cty.setId(rs.getInt(1));
				cty.setName(rs.getString(2));
				cty.setMoney(rs.getString(3));
				cty.setIntroduction(rs.getString(4));
				Imgtype img = new Imgtype();
				img.setId(rs.getInt(5));		
				String sql2 = "select * from imgtype where id =?";
				Object[] object1 = {img.getId()};
				ResultSet rs2 =super.executeQuery(sql2, object1);
				while(rs2.next()){
					img.setId(rs2.getInt(1));
					img.setImg(rs2.getString(2));
					img.setType(rs2.getString(3));
				}
				try {
					super.closeAll(rs2, rs2.getStatement(), rs2.getStatement().getConnection());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				cty.setIid(img);
				cty.setType(rs.getInt(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				super.closeAll(rs, rs.getStatement(), rs.getStatement().getConnection());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cty;
	}

	@Override
	public Commodity chaxundizhi(int id) {
		String sql = "select * from commodity where id = ?";
		Object[] object = {id};
		ResultSet rs =super.executeQuery(sql, object);
		Commodity cty = null;
		try {
			while (rs.next()) {
				cty =new Commodity();
				cty.setId(rs.getInt(1));
				cty.setName(rs.getString(2));
				cty.setMoney(rs.getString(3));
				cty.setIntroduction(rs.getString(4));
				Imgtype img = new Imgtype();
				img.setId(rs.getInt(5));		
				String sql2 = "select * from imgtype where id =?";
				Object[] object1 = {img.getId()};
				ResultSet rs2 =super.executeQuery(sql2, object1);
				while(rs2.next()){
					img.setId(rs2.getInt(1));
					img.setImg(rs2.getString(2));
					img.setType(rs2.getString(3));
				}
				try {
					super.closeAll(rs2, rs2.getStatement(), rs2.getStatement().getConnection());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				cty.setIid(img);
				cty.setType(rs.getInt(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				super.closeAll(rs, rs.getStatement(), rs.getStatement().getConnection());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cty;
	}

	@Override
	public boolean updcomm(Commodity comm) {
		String  sql  = "update `xiangmu`.`commodity` set `name` = ? , `money` = ? , `introduction` = ? , `type` = ? where `id` = ?; ";
		Object[] object = {comm.getName(),comm.getMoney(),comm.getIntroduction(),comm.getType(),comm.getId()};
		return super.executeUpdate(sql, object);
	}

}
